package infraestructura;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import modelo.LineaDePedido;
import modelo.Pedido;
import modelo.Product;
import modelo.User;

public class PedidoPoolPersistence implements PedidoPersistence {

    private DataSource pool = null;
    private static PedidoPoolPersistence persistence = null;

    private PedidoPoolPersistence() {
        Context env = null;
        try {
            env = (Context) new InitialContext().lookup("java:comp/env");
            pool = (DataSource) env.lookup("jdbc/ShoppersPool");
            if (pool == null) {
                throw new RuntimeException("No se ha podido obtener la conexion");
            }
        } catch (NamingException ex) {
            //@Abraham Si usas un logger (que es mejor que mandar consola) ¡hazlo siempre!
            Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static PedidoPersistence getPersistenceManager() {
        if (persistence == null) {
            persistence = new PedidoPoolPersistence();
        }
        return persistence;
    }

    @Override
    public List<Pedido> getALLThePedidos() {
        List<Pedido> pedidos = new ArrayList();

        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
                
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT DISTINCT (Pedido_Id) "
                    + "FROM Pedido;");
//@Abraham ¿algun motivo por el cual no recojas el resulset aqui mismo?
            resultSet = statment.executeQuery();

            while (resultSet.next()) {
                int pedidoId = resultSet.getInt(1);
                pedidos.add(getThePedido(pedidoId));
            }

            return pedidos;

        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return new ArrayList();
    }

    @Override
    public boolean existsThePedido(int pedidoId) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT COUNT(*) "
                    + "FROM Pedido "
                    + "WHERE Pedido_Id = ?;");

            statment.setInt(1, pedidoId);
            
            statment.execute();
            resultSet = statment.getResultSet();

            resultSet.first();

            return resultSet.getInt(1) > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if(resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public Pedido getThePedido(int pedidoId) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT * "
                    + "FROM lineadepedido "
                    + "WHERE Pedido_Id = '" + pedidoId + "';");

            statment.execute();
            resultSet = statment.getResultSet();

            List<LineaDePedido> lineaDePedidos = new ArrayList();

            PersistenceManager persistenceManager = PersistenceManager.getPersistenceManager("db");

            while (resultSet.next()) {
                Product product = persistenceManager.getProduct(resultSet.getInt("Producto_Id"));
                lineaDePedidos.add(new LineaDePedido(product, resultSet.getInt("LineaDePedido_Cantidad")));
            }

            statment.close();
            
            statment = conexion.prepareStatement("SELECT * "
                    + "FROM pedido "
                    + "WHERE Pedido_Id = ? ;");
            
            statment.setInt(1, pedidoId);
            
            statment.execute();

            ResultSet pedidosResultSet = statment.getResultSet();
            pedidosResultSet.next();            

            User user = persistenceManager.getUser(pedidosResultSet.getInt("Usuario_Id"));

            Date date = pedidosResultSet.getDate("Pedido_Fecha");

            return new Pedido(pedidosResultSet.getInt("Pedido_Id"),
                    user, date, lineaDePedidos);

        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return null;
    }

    @Override
    public int add(Pedido pedido) {
        Connection conexion = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        ResultSet resultSetLineaDePedido = null;
        try {
            conexion = pool.getConnection();
            statement = conexion.prepareStatement("SELECT Pedido_Id "
                    + "FROM Pedido "
                    + "ORDER BY Pedido_Id DESC;");

            statement.execute();
            resultSet = statement.getResultSet();
            resultSet.first();
            int id;
            if (resultSet.getRow() != 0) {
                id = resultSet.getInt("Pedido_Id");
            } else {
                id = 0;
            }
            id++;
            statement.close();

            statement = conexion.prepareStatement("INSERT INTO `shoppers`.`pedido` "
                    + "(`Pedido_Id`, `Pedido_Fecha`, `Pedido_Importe`, `Usuario_Id`) "
                    + "VALUES (?, '?', ?, ?);");
            
            statement.setInt(1, pedido.getIdentificador());
            statement.setDate(2, pedido.getDate());
            statement.setDouble(3, calcularPrecioDelPedido(pedido.getLineasDelPedido()));
            statement.setInt(4, pedido.getUser().getId());
            
            statement.executeUpdate();
            statement.close();

            statement = conexion.prepareStatement("SELECT LineaDePedido_Id "
                    + "FROM LineaDePedido "
                    + "ORDER BY LineaDePedido_Id DESC;");
            
            statement.execute();
            resultSetLineaDePedido = statement.getResultSet();
            resultSetLineaDePedido.first();
            int idLinea;
            if (resultSetLineaDePedido.getRow() != 0) {
                idLinea = resultSetLineaDePedido.getInt("LineaDePedido_Id");
            } else {
                idLinea = 0;
            }
            idLinea++;
            statement.close();

            List<LineaDePedido> lineasDePedidos = pedido.getLineasDelPedido();

            for (LineaDePedido linea : lineasDePedidos) {
                statement = conexion.prepareStatement("INSERT INTO `shoppers`.`lineadepedido` "
                        + "(`LineaDePedido_Id`, `Producto_Id`, `Producto_Precio`, "
                        + "`Pedido_Id`, `LineaDePedido_Cantidad`) "
                        + "VALUES (?, ?, ?, ?, ?);");
                
                statement.setInt(1, idLinea);
                statement.setInt(2, linea.getProducto().getId());
                statement.setDouble(3, linea.getProducto().getPrice());
                statement.setInt(4, id);
                statement.setInt(5, linea.getCantidad());
                
                statement.executeUpdate();
                statement.close();
                idLinea++;
            }
             //@Abraham liberar en el finally
            return id;
        } catch (SQLException ex) {
            Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statement != null) {
                        try {
                            statement.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                } finally {
                                    if (resultSetLineaDePedido != null) {
                                        try {
                                            resultSetLineaDePedido.close();
                                        } catch (SQLException ex) {
                                            Logger.getLogger(PedidoPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
         }
        return -1;
    }

    private double calcularPrecioDelPedido(List<LineaDePedido> lineasDelPedido) {
        double precio = 0;
        for (LineaDePedido linea : lineasDelPedido) {
            precio += linea.getProducto().getPrice() * linea.getCantidad();
        }
        return precio;
    }
}